﻿using MySql.Data.MySqlClient;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using NB.MGJ.Model.CodeManage;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NB.MGJ.Service.CodeManage
{
    /// <summary>
    /// dy
    /// 2017年9月18日
    /// 生码批次
    /// </summary>
    public class GenCodeBatchService
    {
        public BaseRepository _baseRepository = new BaseRepository();


        /// <summary>
        /// 获取实体类信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CodeManage_GenCodeBatch GetModel(int id)
        {
            string sql = " select * from CodeManage_GenCodeBatch  where ID=@ID and  ViewFlag=1";
            MySqlParameter[] parameters = {
					new MySqlParameter("@ID", id)
			};
            return _baseRepository.ExecuteSqlCollection<CodeManage_GenCodeBatch>(sql, parameters).FirstOrDefault();
        }
        public int GetModels(int id, string code)
        {

            string sql = string.Format("select count(ID) from CodeManage_GenCode  where PatchId={0} and Status=1 and EnterpriseId={1}", id, code);
            return Convert.ToInt32(_baseRepository.GetSingle(sql));
            //int result = Convert.ToInt32(_baseRepository.GetSingle(sql));         
            //return result;
        }
        /// <summary>
        /// 获取最大批次实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CodeManage_GenCodeBatch GetMaxBatchModel(int eId)
        {
            string sql = " SELECT * FROM `codemanage_gencodebatch` WHERE ViewFlag=1 and EnterpriseId=@EnterpriseId ORDER BY ID DESC limit 1";
            MySqlParameter[] parameters = {
					new MySqlParameter("@EnterpriseId", eId)
			};
            return _baseRepository.ExecuteText<CodeManage_GenCodeBatch>(sql, parameters);
        }

        public DataTable queryCode(string tbName, string fieldName)
        {
            string sql = @"select " + fieldName.TrimEnd(',') + " from {0}";
            sql = string.Format(sql, tbName);
            string connstr = ConfigurationManager.ConnectionStrings["mgj_codesql"].ToString();
            return _baseRepository.GetTable(fieldName, tbName, connstr);
            //return _baseRepository.GetTable(sql, connstr);
        }

        public PagedList<CodeManage_GenCode> GetAProductBatchService(ref PagerModel page, DetailSearch Smodel)
        {

            page.FldName = @" * ";
            page.TblName = @"(SELECT  gc.ID as BID,cg.ID,Batch,Gen_Code_Nums,EnterpriseName,CodeType,Status,gc.ViewFlag,ProductName,BatchName,oe.CreateDate,cg.ChangeDate,cg.ViewDel,cg.EnterpriseId
                                             FROM
                                          CodeManage_GenCode  cg
                                             LEFT JOIN  CodeManage_GenCodeBatch gc ON cg.PatchId = gc.ID
                                             LEFT JOIN Organization_Enterprise oe ON gc.EnterpriseId = oe.ID
                                             LEFT JOIN Product_Product pp ON gc.ProductId = pp.ID
                                             LEFT JOIN Product_Batch pb ON pb.ID = gc.ProductBatchId) z";
            string StrCondition = "   1=1  ";

            if (string.IsNullOrWhiteSpace(page.StrCondition))
            {
                StrCondition = "   1=1 ";
            }
            else
            {
                StrCondition = page.StrCondition;
            }

            if (!string.IsNullOrWhiteSpace(Smodel.Batch))
            {
                StrCondition += "  and (Batch like '%" + Smodel.Batch + "%')";
            }

            if (!string.IsNullOrWhiteSpace(Smodel.ProductName))
            {
                StrCondition += " and (ProductName like '%" + Smodel.ProductName + "%')";
            }
            if (!string.IsNullOrWhiteSpace(Smodel.EnterpriseName))
            {
                StrCondition += " and (EnterpriseName like '%" + Smodel.EnterpriseName + "%')";
            }
            if (!string.IsNullOrWhiteSpace(Smodel.StartDate))
            {
                StrCondition += " and  (left(CreateDate,10) >= '" + Smodel.StartDate + "')";
            }
            if (!string.IsNullOrWhiteSpace(Smodel.EndDate))
            {
                StrCondition += " and  (left(CreateDate,10) <= '" + Smodel.EndDate + "')";
            }
            #region MyRegion
            //if (Smodel.Batch != null)
            //{
            //    StrCondition += "  and (Batch like '%" + Smodel.Batch + "%')";
            //}
            //if (Smodel.ProductName != null)
            //{
            //    StrCondition += " and (ProductName like '%" + Smodel.ProductName + "%')";
            //}
            //if (Smodel.EnterpriseName != null)
            //{
            //    StrCondition += " and (EnterpriseName like '%" + Smodel.EnterpriseName + "%')";
            //}
            ////if (Smodel !=null)
            ////{
            ////    StrCondition += " and ViewDel=0 and (z.Batch like '%" + Smodel.Batch + "%') and (z.ProductName like '%" + Smodel.ProductName + "%')and (z.EnterpriseName like '%" + Smodel.EnterpriseName + "%') ";
            ////} 
            //if (Smodel.StartDate != "" && Smodel.StartDate != null)
            //{
            //    StrCondition += " and  (left(CreateDate,10) >= '" + Smodel.StartDate + "')";
            //}
            //if (Smodel.EndDate != "" && Smodel.EndDate != null)
            //{
            //    StrCondition += " and  (left(CreateDate,10) <= '" + Smodel.EndDate + "')";
            //}
            #endregion

            page.StrCondition = StrCondition;

            if (string.IsNullOrEmpty(page.FldSort))
            {
                page.FldSort = "ID  desc ";
            }
            //if (string.IsNullOrEmpty(page.FldSort) || page.FldSort == "")
            //    page.FldSort = "ID  desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref page);
            IEnumerable<CodeManage_GenCode> resultList = _baseRepository.ExecuteStoredProcedureCollection<CodeManage_GenCode>("Common_PageList", parameters);
            return new PagedList<CodeManage_GenCode>(resultList, page.Page, page.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
		/// <summary>
		/// 删除
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public int DelCodeBatchService(int id) {
			CodeManage_GenCode cg = new CodeManage_GenCode();
			cg.ViewDel = 1;
			cg.ID= id;
			cg.ChangeDate = DateTime.Now;
			return _baseRepository.Update<CodeManage_GenCode>(cg);
		}
        public CodeManage_GenCode GetcodebatchModelId(int BatchID)
        {
            string sql = @"SELECT cg.ID,cd.ID as CardID,CodeType,Status,ExpiryDate,cg.ChangeDate,ActiveType,Gen_Code_Nums,Query_Message_Content,Query_Result_Content,Query_Tel_Content,Anti_Fake_Num,Logistics_Num,LabelImage,PatchId,EnterpriseName,Batch,ProductName,ProductId,P.TypeId,cg.EnterpriseId,ProductBatchId,cd.Title,cd.LabelImgUrl,cd.ProductImgUrl,cg.EnterpriseUse FROM CodeManage_GenCode cg LEFT JOIN Organization_Enterprise oe ON cg.EnterpriseId = oe.ID LEFT JOIN CodeManage_GenCodeBatch cgc ON cg.PatchId = cgc.ID LEFT JOIN product_product p ON p.ID = cgc.ProductId LEFT JOIN product_producttypes ps ON ps.ID = p.TypeId LEFT JOIN codemanage_card cd ON cd.BatchID = cgc.ID  where cd.BatchID ={0} ";
            sql = string.Format(sql, BatchID );
            var result = _baseRepository.ExecuteSqlCollection<CodeManage_GenCode>(sql).FirstOrDefault();
			return result;
		}
        public string filename(string code)
        {
                string strSql = "SELECT EnterpriseName from organization_enterprise where Code='" + code + "'";
                return _baseRepository.ExecuteText<string>(strSql);
         
        }
        public string batch(int pid)
        {
            string strSql = "SELECT batch from codemanage_gencode a LEFT JOIN codemanage_gencodebatch b ON a.PatchId=b.ID where PatchId=" + pid;
            return _baseRepository.ExecuteText<string>(strSql);

        }

    }
}
